180. Consecutive Numbers.sql

-----------------------------------------------------------
--Solution 1 :
-----------------------------------------------------------
WITH cte AS(
SELECT id,Num,
 LEAD(Num,1) OVER() as Next1,
 LEAD(Num,2) OVER() as Next2
FROM logs_180
)
SELECT DISTINCT Num AS ConsecutiveNums
FROM cte
WHERE Num = Next1 AND Num = Next2;


-----------------------------------------------------------
--Solution 2 :
-----------------------------------------------------------

WITH cte AS(
SELECT id,Num,
 LAG(Num) OVER() as Prev,
 LEAD(Num) OVER() as Next
FROM logs_180
)
SELECT DISTINCT Num AS ConsecutiveNums
FROM cte
WHERE Num = Prev AND Num = Next;


-----------------------------------------------------------
--Solution 3 :
-----------------------------------------------------------
SELECT DISTINCTl1.NumAS ConsecutiveNums
FROM logs_180 l1
JOIN logs_180 l2 ONl1.id=l2.id-1ANDl1.Num=l2.Num
JOIN logs_180 l3 ONl1.id=l3.id-2ANDl2.Num=l3.Num


-----------------------------------------------------------
--Exensible Solution (Best) :
-----------------------------------------------------------

WITH ranked AS (
SELECT*,
 (id-ROW_NUMBER() OVER (PARTITION BY num ORDER BY id)) AS diff
FROM logs_180
)
SELECT DISTINCT num AS"ConsecutiveNums"
FROM ranked
GROUP BY diff,num
HAVINGCOUNT(id) >=3;